﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data;

namespace timetable_get_info
{
    public class Program
    {
        DBconnection myDB;

        DataTable MyQuery( string cID)
        {
           int i,j;
           string query = "";
           DataTable dt = new DataTable();
           try
           {
               myDB = new DBconnection();

               query = @"SELECT DISTINCT a.teacher_id, a.subject_type_id, a.class_id, b.subject_code, c.total_slot, c.max_continuous_slot, c.subject_weight
                            FROM teacher_subject_type_allocation AS a, subject_type AS b, subject AS c, class AS d
                            WHERE d.class_id='@cID'
                            AND d.class_teacher_id=a.teacher_id
                            AND a.subject_type_id=b.subject_type_id
                            AND b.subject_code=c.subject_code
                            AND a.class_id=d.class_id

                            UNION

                            SELECT *
                            FROM(SELECT DISTINCT a.teacher_id, a.subject_type_id, a.class_id, b.subject_code, c.total_slot, c.max_continuous_slot, c.subject_weight
                            FROM teacher_subject_type_allocation AS a, subject_type AS b, subject AS c, class AS d
                            WHERE d.class_id='@cID'
                            AND NOT d.class_teacher_id=a.teacher_id
                            AND a.subject_type_id=b.subject_type_id
                            AND b.subject_code=c.subject_code
                            AND a.class_id=d.class_id
                            ORDER BY subject_weight, teacher_id) AS k";
               query = query.Replace("@cID", cID);
               dt = myDB.Search(query);

               String[,] table = new String[dt.Rows.Count, dt.Columns.Count];

               for (i = 0; i < dt.Rows.Count;i++)
               {
                   for (j = 0; j < dt.Columns.Count; j++)
                   {
                       table[i, j] = dt.Rows[i][j].ToString();

                       Console.Write(table[i, j] + "\t");
                   }
                   Console.Write("\n");
               }
               Console.ReadLine();

           }
           catch (Exception ex)
           {
               throw ex;
           }
           return dt;
          
        }

        public static void Main(string[] args)
        {
                
                Console.WriteLine("Enter the Class Id:");
                string cID = Console.ReadLine();

                Program a = new Program();
                a.MyQuery(cID);

        }
    }
}
